-- @owner: Alice
-- @date: 2025-09-03
-- @testpoint: 确定性函数支持result_cache,pgplsql的函数，合理报错
@conn PrimaryDbAmode;
drop extension if exists gms_output;
drop function if exists func_increment_plsql;
drop function if exists f_test_13;
create extension gms_output;
--case1
set enable_func_cache = 1;
select gms_output.enable();
set query_dop=1;


set behavior_compat_options='skip_insert_gs_source,select_into_return_null,proc_outparam_override';
--创建表
drop table if exists tt_cache;
create table tt_cache(
id int,
col_smallint smallint
);
insert into tt_cache values(
generate_series(1,20),
generate_series(1,20)%3
);
select count(*) from tt_cache;
create or replace function func_increment_plsql(i integer) RETURNS integer result_cache AS $$
BEGIN
raise notice 'NOTICE: excute function...';
RETURN i + 1;
END;
$$ LANGUAGE plpgsql stable;
/
select func_increment_plsql(12);
select func_increment_plsql(col_smallint) from tt_cache;
select func_increment_plsql(func_increment_plsql(col_smallint)) from tt_cache;
select func_increment_plsql(12) from tt_cache;
--case2
create or replace function f_test_13(a varchar2) RETURNS NUMBER stable AS $$
DECLARE
v_cnt NUMBER;
x INTEGER;
FUNCTION f (n INTEGER)
RETURN INTEGER stable result_cache
IS
BEGIN
raise notice 'NOTICE: excute function...';
RETURN (n*n);
END;
BEGIN
raise notice 'NOTICE: excute function...';
gms_output.put_line (
'f returns ' || f(2) || '. Execution returns here (1).'
);
x := f(2);
gms_output.put_line('Execution returns here (2).');
RETURN f(3);
END;
$$ LANGUAGE plpgsql;
/
select f_test_13(12);
select f_test_13('aaa') from tt_cache;
select f_test_13(func_increment_plsql(col_smallint)) from tt_cache;
select f_test_13(12) from tt_cache;
drop function if exists func_increment_plsql(integer);
--teardown
drop function if exists func_increment_plsql;
drop function if exists f_test_13;
